package com.led.datas;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;

import net.sf.json.JSONArray;

import com.led.datatype.DataBasic;
import com.led.datatype.DataReal;
import com.led.datatype.LedData;
import com.led.datatype.UserInfo;
import com.led.db.DBMgr;

public class DataAccess {
	
	//query all include other who can be seemed
	static final String SQL_QUERY_DATAS_ALL = 
			"select b.id, b.info, u.name, d.datas "
			+ " from datas_basic b, datas_real d, userInfo u "
		   + " where b.idx_datas = d.id and b.userId = u.id and "
		   + " (u.id = ? or u.canOtherSee = 1) limit 100";
	static final String SQL_QUERY_DATAS_BY_USERID = 
			"select b.id, b.info, u.name, d.datas "
			+ " from datas_basic b, datas_real d, userInfo u "
		   + " where b.idx_datas = d.id and b.userId = u.id and "
		   + "  u.id = ? limit 100";
	

	static final String SQL_QUERY_DATAS_REAL_BY_IDX = 
			"select datas from datas_real where id = ?";


	
	static final String SQL_INSERT_DATAS_BASIC = 
			"insert into datas_basic(idx_datas, info, userId) values(?,?,?)";
	
	static final String SQL_INSERT_DATAS_REAL = 
			"insert into datas_real(datas) values(?)";
	
//	public static 
	public static void main(String[] args) {
		System.out.println( getAllDatas("public", "public"));
//		System.out.println( getDatasBasic());
		System.out.println( getDatasReal(1));
//		System.out.println(saveToDB("{\"info\":\"test 2\",\"datas\":\"2 2 2 2\"}"));
	}

	public static String getDatasReal(int idxRealDatas){
		Connection con = DBMgr.getConnect();
		if(null == con){
			System.out.println("when getDatasReal, con is null!");
		}

		JSONArray js = new JSONArray();
		ResultSet rs =null;
		PreparedStatement ps = null;
		try {
			ps = con.prepareStatement(SQL_QUERY_DATAS_REAL_BY_IDX);
			ps.setInt(1, idxRealDatas);;
			rs = ps.executeQuery();
			if(null == rs){
				System.out.println("no datas exists in getDatasReal, idx:" + idxRealDatas);
				return "";
			}

			LinkedList<DataReal> dataList = DataReal.getDatas(rs);
			for(DataReal ld: dataList){
				js.add(ld.toJsonStr());
			}
		} catch (SQLException e) {
			e.printStackTrace();
			DBMgr.reset();
		} finally{
			if(null != rs){
				try{rs.close();} catch(Exception e){}
			}
			if(null != ps){
				try {ps.close();} catch (SQLException e) {e.printStackTrace();}
			}
		}
		return js.toString();
	}
	public static String getAllDatas(String userName, String password){
		UserInfo userInfo = UserAccess.getUserInfoByNamePassword(userName, password);
		return getAllDatas(userInfo);
	}
	public static String getAllDatas(UserInfo userInfo){
		Connection con = DBMgr.getConnect();
		if(null == con){
			System.out.println("when getAllDatas, con is null!");
		}
		JSONArray js = new JSONArray();
		ResultSet rs = null;
		try{
			rs = getAllDatasResultSet(userInfo);
			LinkedList<LedData> dataList = LedData.getDatas(rs);
			for(LedData ld: dataList){
				js.add(ld.toJsonStr());
			}
		} catch (Exception e) {
			e.printStackTrace();
			DBMgr.reset();
		} finally{
			if(null != rs){
				try{rs.close();} catch(Exception e){}
			}
//			if(null != ps){
//				try {ps.close();} catch (SQLException e) {e.printStackTrace();}
//			}
		}
		return js.toString();
	}

	public static ResultSet getAllDatasResultSet(String userName, String password, String noNeedSeeOthers){
		System.out.println("enter getAllDatasResultSet(userName:" + userName +", seeOthers:" + noNeedSeeOthers + ")" );
		UserInfo userInfo = UserAccess.getUserInfoByNamePassword(userName, password);
		if(null == userInfo){
			return null;
		}
		if(null != noNeedSeeOthers){
			try{
				userInfo.setCanSeeOthers(1 - Integer.parseInt(noNeedSeeOthers));
			} catch(Exception e){
				e.printStackTrace();
			}
		}
		return getAllDatasResultSet(userInfo);
	}
	private static ResultSet getAllDatasResultSet(UserInfo userInfo){
		Connection con = DBMgr.getConnect();
		if(null == con){
			System.out.println("when getAllDatas, con is null!");
			return null;
		}
		ResultSet rs =null;
		PreparedStatement ps = null;
		try {
			if(userInfo.getCanSeeOthers() == 1){
				ps = con.prepareStatement(SQL_QUERY_DATAS_ALL);
			} else {
				ps = con.prepareStatement(SQL_QUERY_DATAS_BY_USERID);
			}
			ps.setInt(1, userInfo.getId());
			rs = ps.executeQuery();
			return rs;
		} catch (SQLException e) {
			e.printStackTrace();
			DBMgr.reset();
		} finally {
//			if(null != ps){
//				try {ps.close();} catch (SQLException e) {e.printStackTrace();}
//			}
		}
		return null;
	}

//	public static boolean saveToDB(String jsLedData){
//		//maybe insert datas is invalid!
//		LedData ledData  = null;
//		try{
//			String jsDatas = jsLedData.replaceAll("\t|\r|\n", " "); //
//			jsDatas = jsDatas.replaceAll("[ ]+", " ");
//			ledData  = new LedData(JSONObject.fromObject(jsDatas));
//		} catch (Exception e){
//			e.printStackTrace();
//			return false;
//		}
//		return saveToDB(ledData);
//	}
	public static boolean saveToDB(LedData ledData){
		Connection con = DBMgr.getConnect();
		if(null == con){
			System.out.println("when in SaveToDB, connect is null");
			return false;
		}
		
		PreparedStatement ps = null;
		 try {
			 con.setAutoCommit(false);
//			 final String insertSql = "insert into leddatas(info, datas) values(?, ?)";
			 ps = con.prepareStatement(SQL_INSERT_DATAS_REAL);
			 ps.setString(1, ledData.getDatas());
			 int result = ps.executeUpdate();
			 if(result != 1){
				 System.out.println("insert " + ledData + " success, tab:datas_real"
				 		+ ", pos:" + result);
				 con.rollback();
				 return false;
			 }

			 int pos = getLastInsertNum(con);
			 if(pos <= 0){
				System.out.println("call getLastInsertNum after insert to datas_real failed! pos:" + pos);
				con.rollback();
				return false;
			 } else {
				System.out.println("call getLastInsertNum after insert to datas_real success! pos:" + pos);
			 }

			 ps = con.prepareStatement(SQL_INSERT_DATAS_BASIC);
			 ps.setInt(1, pos);
			 ps.setString(2, ledData.getInfo());
			 ps.setInt(3, ledData.getUserId());
			 result =  ps.executeUpdate();
			 if(result == 1){
				 System.out.println("insert " + ledData + " success, result:" + result);
				 con.commit();
			 } else {
				 System.out.println("insert " + ledData + " failed!, result:" + result);
				 con.rollback();
				 return false;
			 }
			 return true;
		} catch (SQLException e) {
			try {
				con.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}

			DBMgr.reset();
			e.printStackTrace();
		} finally {
			try{ con.setAutoCommit(true);} catch(Exception e){e.printStackTrace();}
			if(null != ps){
				try {ps.close();} catch (SQLException e) {}
			}
		}
		return false;
	}

	public static boolean removeDataByIDRealData(int idxRealData){
		System.out.println("enter removeDataByIDRealData(idxRealData:" + idxRealData + ")");
		Connection con = DBMgr.getConnect();
		if(null == con){
			System.out.println("when in SaveToDB, connect is null");
			return false;
		}
		String DEL_DATAS_REAL_BY_ID = "delete from datas_real where id = ?";
		String DEL_DATAS_BASIC_BY_ID_REAL = "delete from datas_basic where idx_datas = ?";
		PreparedStatement ps = null;
		 try {
			 con.setAutoCommit(false);
			 ps = con.prepareStatement(DEL_DATAS_REAL_BY_ID);
			 ps.setInt(1, idxRealData);
			 ps.execute();
			 
			 ps = con.prepareStatement(DEL_DATAS_BASIC_BY_ID_REAL);
			 ps.setInt(1, idxRealData);
			 ps.execute();
			 con.commit();
			 return true;
		} catch (SQLException e) {
			try {
				con.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}

			DBMgr.reset();
			e.printStackTrace();
		} finally {
			try{ con.setAutoCommit(true);} catch(Exception e){e.printStackTrace();}
			if(null != ps){
				try {ps.close();} catch (SQLException e) {e.printStackTrace();}
			}
		}
		return false;
	}
	private static int getLastInsertNum(Connection con ){
		 PreparedStatement ps = null;
		 ResultSet rs = null;
		 try {
			ps = con.prepareStatement("select LAST_INSERT_ID()");
			rs = ps.executeQuery();
			if(null == rs || rs.isAfterLast()){
				System.out.println("call getLastInsertNum failed! rs is null or after last");
				return -1;
			} else {
				rs.next();
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(null != rs){
				try{rs.close();} catch(Exception e){}
			}
			if(null != ps){
				try {ps.close();} catch (SQLException e) {e.printStackTrace();}
			}
		}
		return -1;
	}
}
